# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: http://doc.scrapy.org/en/latest/topics/item-pipeline.html
import codecs, json
from scrapy.exporters import JsonItemExporter
from baidutop.items import BaidutopItem

class JsonExporterPipeline(object):
    def __init__(self):
        self.file = codecs.open('articlejson.json', 'wb', encoding="utf-8")
        # self.exporter = JsonItemExporter(self.file, encoding="utf-8", ensure_ascii=False)
        # self.exporter.start_exporting()

    def spider_closed(self, spider):
        # self.exporter.finish_exporting()
        self.file.close()

    def process_item(self, item, spider):
        # self.exporter.export_item(item)
        if isinstance(item, BaidutopItem):
            lines = json.dumps(dict(item), ensure_ascii=False) + "\n"
            self.file.write(lines)
        return item


# 爬取到的数据写入到SQLite数据库
import sqlite3

class SQLitePipeline(object):

    #打开数据库
    def open_spider(self, spider):
        db_name = spider.settings.get('SQLITE_DB_NAME', 'scrapy.db')

        self.db_conn = sqlite3.connect(db_name)
        self.db_cur = self.db_conn.cursor()

        # 检查数据库中 表是否存在
        if(not self.check_table()):
            #表如果不存在
            self.crate_table()

    #关闭数据库
    def close_spider(self, spider):
        self.db_conn.commit()
        self.db_conn.close()

    #对数据进行处理
    def process_item(self, item, spider):
        if isinstance(item, BaidutopItem):
            self.insert_db(item)
        return item

    def check_table(self):
        sql = "select count(*) from sqlite_master where type = 'table' and name = 'BaiduNews'"
        self.db_cur.execute(sql)
        if (self.db_cur.fetchall()[0][0] >= 1):
            return True
        else:
            return False

    def crate_table(self):
        sql = " CREATE TABLE [BaiduNews] ( \
              [title] VARCHAR2(20), \
              [hot] NVARCHAR2(10), \
              [link] VARCHAR2(200))"
        return self.db_cur.execute(sql)
       
    #插入数据
    def insert_db(self, item):
        values = []
        for key in item.keys():
            values.append(item[key][0])
        values = tuple(values)

        sql = 'INSERT INTO BaiduNews VALUES(?,?,?)'
        self.db_cur.execute(sql, values)